package in.muton.edushare.documentModel.excel;


import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelUtils {
	
	/**
	 * <p>
	 * Reads XLS files provided via input stream. Returns data in rows as String
	 * arrays in a list. numberOfColumns are columns to be read from first
	 * column (number of columns=5, means first 5 columns will be read) startRow
	 * is rowNumber which reading should be started from. Please note row number
	 * is a zero based index. So, to start from 3rd row you need to provide
	 * startRow = 2.
	 * </p>
	 * <p>
	 * If startRow is more than number of rows in the file it will throw
	 * OutOfIndexException.
	 * </p>
	 * <p>
	 * Only first sheet in the book will be read.
	 * </p>
	 * 
	 * @param inputStream
	 * @param numberOfColumns
	 * @param startRow
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static List<String[]> readExcelSheet(InputStream inputStream,
			int numberOfColumns, int startRow) throws InvalidFormatException,
			IOException {
		Workbook wb = WorkbookFactory.create(inputStream);
		Sheet sheet = wb.getSheetAt(0);
		if(sheet.getLastRowNum()<startRow)
			throw new ArrayIndexOutOfBoundsException(
					"Either the sheet is empty or startRow is more than available rows. Last Row is: "
							+ sheet.getLastRowNum());
		List<String[]> data = new ArrayList<String[]>();
		for(int i=startRow; i<=sheet.getLastRowNum(); i++){
			if(sheet.getRow(i)!=null)//ignore empty rows
			{
				String[] row = new String[numberOfColumns];
				for(int j=0; j < numberOfColumns; j++){
					row[j] = ExcelHelper.getCellValueAsString(sheet.getRow(i)
							.getCell(j));
				}
				data.add(row);
			}
		}
		return data;
	}

	public static int getNumRows(InputStream inputStream)
			throws InvalidFormatException, IOException{
		Workbook wb = WorkbookFactory.create(inputStream);
		Sheet sheet = wb.getSheetAt(0);
		return sheet.getLastRowNum()+1;
}

	public static int getNumColumns(InputStream inputStream, int row)
			throws InvalidFormatException, IOException {
		Workbook wb = WorkbookFactory.create(inputStream);
		Sheet sheet = wb.getSheetAt(0);
		if (sheet.getLastRowNum() < row)
			throw new ArrayIndexOutOfBoundsException(
					"Either the sheet is empty or startRow is more than available rows. Last Row is: "
							+ sheet.getLastRowNum());
		int i = -1;
		if (sheet.getRow(row) != null)// ignore empty rows
		{
			String rowData = "";
			do {
				rowData = ExcelHelper.getCellValueAsString(sheet.getRow(row)
						.getCell(i+1));
				i++;
			} while (StringUtils.isValid(rowData));
		}
		return i;
	}

}
